import excel "build/input/bbg/bbg_crypto_futures_pseudo.xlsx", sheet("to_stata") firstrow clear

ds date, not
destring `r(varlist)', ignore("#N/A N/A") replace

// calculate z 
forval x = 1/6 {
	replace ois_`x'm = ois_`x'm/100
	gen z_`x'm = 1/(1+ois_`x'm/12)^(`x'/12)
}

// exclude dates that are rollover dates
drop if round(BRRIndex, 1) == BTC1Comdty

// calculate expected return
forval x = 2/5 {
	gen e_rBTC_`x'm = ((z_`x'm * BTC`x'Comdty)/(z_1m * BTC1Comdty))-1
	gen e_rBTC_`x'm_annualized = ((e_rBTC_`x'm+1)^(12/(`x'-1))-1)*100
	
	gen e_rETH_`x'm = ((z_`x'm * DCR`x'Curncy)/(z_1m * DCR1Curncy))-1
	gen e_rETH_`x'm_annualized = ((e_rETH_`x'm+1)^(12/(`x'-1))-1)*100
	
	gen e_rSP_`x'm = ((z_`x'm * ES`x'Index)/(z_1m * ES1Index))-1
	gen e_rSP_`x'm_annualized = ((e_rSP_`x'm+1)^(12/(`x'-1))-1)*100

}

// create cme business cal
preserve
keep date ois_1m
duplicates drop
capture drop(bcaldate)
drop if missing(ois_1m)
bcal create b_cal_cme, from(date) personal replace
restore
bcal load b_cal_cme
gen bcaldate = bofd("b_cal_cme",date)
drop if missing(bcaldate)
assert !missing(bcaldate) if !missing(date)
format %tbb_cal_cme bcaldate
tsset bcaldate

// calculate bitcion and ether refernce price returns
gen r_btc = BRRIndex/l.BRRIndex-1
gen r_eth = ETHUSDRRIndex/l.ETHUSDRRIndex-1

drop bcaldate

tsset date
save "build/output/bbg_crypto_futures.dta", replace




